/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package onpu_dms_xls.kernel;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;


import onpu_dms_xls.kernel.common.Util;
import onpu_dms_xls.exceptions.*;
import onpu_dms_xls.kernel.common.VariableDeclaration;
import onpu_dms_xls.kernel.common.VariableType_DBRelationTypes;
import onpu_dms_xls.kernel.common.VariableType_InputRelationTypes;
import onpu_dms_xls.kernel.common.VariableType_XLSVisibilityTypes;
import onpu_dms_xls.kernel.common.XLS.XLS_Variables;
import onpu_dms_xls.kernel.common.XLS.XLS_Variable;
import onpu_dms_xls.kernel.common.inputParameters.ToDB_InputParameters;
import onpu_dms_xls.kernel.common.logAndDebug.LoggingFacade;
import org.apache.log4j.Logger;

/**
 *
 * @author snbm
 */
public class XLS_2_DB {
    

    public void read_XLS_And_Process_It_To_DB(String xml, Connection con, File inFile) throws MyException {
        /*
         приблизно:
        1.+ конект до БД
        2.+ зчитування шаблоного ХМЛ в структуру (varDeclArray, тип - VariableDeclaration[])
        3.+ зчитування шаблоного ХЛС в структуру (XLSVars, тип - XLS_Variables)
        4.+ заповнення в varDeclArray тих змінних 1 рівня, які не в ітераційному ряді з varDeclArray
        даними з вхідного ХЛС по координатам, які вказані в XLSVars
        5.+ генерація/розрахунок змінних 0 рівня-не ітераційних
        6. в циклі:
        -зчитування тих змінних 1 і 2 рівня, які в ітераційному ряді varDeclArray
        з вхідного ХЛС по координатам, які вказані в XLSVars
        -розрахунок змінних 0 рівня в ітераційному ряді
        -заливання змінних 2 рівня в БД
         */

        String tempFolder = Util.getTempFolderFromInputXML(xml);

        ToDB_InputParameters input;
        try {
            input = new ToDB_InputParameters(xml, tempFolder);
        } catch (IOException e) {
            throw new MyException("make_XLS_File_From_DB", "FromDB_InputParameters input = new FromDB_InputParameters(xml);", e.getMessage());
        }
        VariableDeclaration[] varDeclArray = null;
        XLS_Variables XLSVars = null;

        //1
        Connection conn = con;
        
        //2
        TemplatesLoader tl = TemplatesLoader.getTemplatesLoader(TemplatesLoader.TemplatesLoaders.DB);
        
        File templateXML = tl.getTemplateFile(input.getTemporaryFolder(), input.getDocumentClassId(), conn, TemplatesLoader.TemplatesLoaderFiles.XML_FILE);
        LoggingFacade.trace.info("opening... template XML" + templateXML.getAbsolutePath());
        try {
            varDeclArray = Util.generateVariablesDeclarationsArrayFromXMLFile(templateXML);
        } catch (FileNotFoundException e) {
            throw new MyXMLException("XLS_2_DB", "read_XLS_And_Process_It_To_DB/2 section", "calling to Common.generateVariablesDeclarationsArrayFromXMLFile(templateXML)", e.getMessage());
        }
        
        LoggingFacade.trace.info("read from XML:");
        for (VariableDeclaration vd : varDeclArray) {
            LoggingFacade.trace.info(vd.toString());
        }

        //3
        File templateXLS = tl.getTemplateFile(input.getTemporaryFolder(), input.getDocumentClassId(), conn, TemplatesLoader.TemplatesLoaderFiles.XLS_FILE);
        LoggingFacade.trace.info("opening... template XLS" + templateXLS.getAbsolutePath());
        try {
            XLSVars = new XLS_Variables(templateXLS);
        } catch (Exception e) {
            throw new MyXLSException("XLS_2_DB", "read_XLS_And_Process_It_To_DB/3 section", "getting a template XLS", e.getMessage());
        }
        
        //4.1 open input XLS
        File inputXLS = inFile;
        LoggingFacade.trace.info("opening... input XLS: " + inputXLS.getAbsolutePath());
        if (!inputXLS.canRead()) {
            throw new MyXLSException("XLS_2_DB", "read_XLS_And_Process_It_To_DB/4.1 section", "getting a input XLS", "no input XLS found");
        }

        //4
        this.fillVarDeclArrays1levelNoIterationVariablesWithXLSFileData(
                varDeclArray,
                XLSVars,
                inputXLS);
        
        //5
        this.get0levelNoIterationVariables(varDeclArray, conn);
        
        //6
        this.iterationVariablesProcessing(varDeclArray, XLSVars, inputXLS, conn);
        
        try {
            templateXLS.delete();
            templateXML.delete();
        } catch (Exception e) {
            throw new MyXMLException("FromDB", "while deleting temporary templates files", "", e.getMessage());
        }
        //dbc.closeConnection();
    }

    /*
    заповнення в varDeclArray тих змінних 1 рівня, які не в ітераційному ряді з varDeclArray
    даними з вхідного ХЛС по координатам, які вказані в XLSVars
     */
    private void fillVarDeclArrays1levelNoIterationVariablesWithXLSFileData(
            VariableDeclaration[] varDeclArray,
            XLS_Variables XLSVars,
            File inputXLS) throws MyException {

        InputStream inp;
        HSSFWorkbook wb;
        HSSFSheet sheet;
        try {
            inp = new FileInputStream(inputXLS.getAbsoluteFile());
            wb = new HSSFWorkbook(inp);
            sheet = wb.getSheetAt(0);
        } catch (IOException e) {
            throw new MyXLSException(
                    "XLS_2_DB",
                    "fillVarDeclArrays1levelNoIterationVariablesWithXLSFileData",
                    "error while opening XLS input file",
                    e.getMessage());
        }

        for (VariableDeclaration vd : varDeclArray) {
            if (vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.SELECT) &&
                vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT) &&
                vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE) &&
                !vd.isInIteration()) {
                XLS_Variable xlsv = XLSVars.getVariableFromName(vd.getVarName());
                int row = xlsv.getRow();
                int col = xlsv.getCol();
                LoggingFacade.trace.info("for XML variable:" + vd.getVarName() + " found variable in template XLS:" + xlsv.getName()
                        + "with coordinates: row" + row + " col:" + col);
                Cell inputXLSCell = sheet.getRow(row).getCell(col);
                String cellValue = Util.getCellData(inputXLSCell);
                LoggingFacade.trace.info("appropriate input XLS cell data: " + cellValue);
                vd.setValue(cellValue);
                LoggingFacade.trace.info("set value for:" + vd);
            }
        }
    }

    private void get0levelNoIterationVariables(
            VariableDeclaration[] varDeclArray,
            Connection conn)
            throws MyException {
        LoggingFacade.trace.info("get0levelNoIterationVariables");
        for (VariableDeclaration vd : varDeclArray) {
            if (vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.SELECT) &&
                vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.SOMETIMES_IN_INPUT) &&
                vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.NOT_VISIBLE) &&
                !vd.isInIteration()) {
                LoggingFacade.trace.info("|found variable:" + vd.getVarName());
                LoggingFacade.trace.info("|variable query before replacement:" + vd.getFromDBQuery());

                String fromDBQuery = Util.fillQueryWithVariablesValues(vd.getFromDBQuery(), varDeclArray);

                LoggingFacade.trace.info("|variable query after replacement:" + fromDBQuery);

                String queryResult = "";
                try {
                    Statement st = conn.createStatement();
                    ResultSet rs = st.executeQuery(fromDBQuery);
                    rs.next();
                    queryResult = rs.getString(1);
                    st.close();
                } catch (SQLException e) {
                    //that happens
                    if (e.getMessage().equals("ResultSet not positioned properly, perhaps you need to call next.")) {
                        //queryResult = "n/d";
                        //but here it is no acceptable
                        throw new MyXMLException("XLS_2_DB", "get0levelNoIterationVariables", "error while getting value for variable:" + vd.getVarName(), "no query result");
                    } else {
                        throw new MySQLException(
                                "XLS_2_DB",
                                "get0levelNoIterationVariables",
                                fromDBQuery,
                                "",
                                e.getMessage());
                    }
                }
                LoggingFacade.trace.info("|queryResult:" + queryResult);
                vd.setValue(queryResult);
                LoggingFacade.trace.info("|" + vd);
            }
        }
    }

    /**
    6. в циклі:
    -зчитування тих змінних 1 і 2 рівня, які в ітераційному ряді varDeclArray
    з вхідного ХЛС по координатам, які вказані в XLSVars
    -розрахунок змінних 0 рівня в ітераційному ряді
    -заливання змінних 2 рівня в БД
     */
    private void iterationVariablesProcessing(
            VariableDeclaration[] varDeclArray,
            XLS_Variables XLSVariables,
            File inputXLS,
            Connection conn) throws MyException {
            
        LoggingFacade.trace.info("iterationVariablesProcessing");

        InputStream inp;
        HSSFWorkbook wb;
        HSSFSheet sheet;
        try {
            inp = new FileInputStream(inputXLS.getAbsoluteFile());
            wb = new HSSFWorkbook(inp);
            sheet = wb.getSheetAt(0);
        } catch (IOException e) {
            throw new MyXLSException(
                    "XLS_2_DB",
                    "fillVarDeclArrays1levelNoIterationVariablesWithXLSFileData",
                    "error while opening XLS input file",
                    e.getMessage());
        }

        //get a first row of table
        int firstRowOfTable = -1;
        for (VariableDeclaration vd : varDeclArray) {
            if (vd.isInIteration() &&
                        vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT) &&
                        vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE)) {
                LoggingFacade.trace.info("first variable in iteration row:" + vd.getVarName());
                firstRowOfTable = XLSVariables.getVariableFromName(vd.getVarName()).getRow();
                LoggingFacade.trace.info("firstRowOfTable: " + firstRowOfTable);
                break;
            }
        }
        if (firstRowOfTable == -1) {
            throw new MyXMLException("XLS_2_DB", "iterationVariablesProcessing", "didn't get first row of table", "very unexpeted error");
        }

        //loop on table
        boolean tableHasNewRows = true;
        int currentRowInTable = firstRowOfTable;
        while (tableHasNewRows) {
            //get 1 and 2 level variables values from XLS
            for (VariableDeclaration vd : varDeclArray) {
                if (vd.isInIteration() &&
                        vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT) &&
                        vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE)) {
                    int col = XLSVariables.getVariableFromName(vd.getVarName()).getCol();
                    String value = Util.getCellData(sheet.getRow(currentRowInTable).getCell(col));
                    LoggingFacade.trace.info("for variable:" + vd.getVarName() + " read value " + value);
                    vd.setValue(value);
                }
            }

            //get the value for 0 level iteration variables
            for (VariableDeclaration vd : varDeclArray) {
                if (vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.SELECT) &&
                        vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT) &&
                        vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.NOT_VISIBLE) &&
                        vd.isInIteration()) {
                    LoggingFacade.trace.info("found 0 level variable: " + vd.getVarName()
                            + "\nvariable from DB query before replacing: " + vd.getFromDBQuery());
                    String fromDBQuery = Util.fillQueryWithVariablesValues(vd.getFromDBQuery(), varDeclArray);

                    LoggingFacade.trace.info("variable from DB query after replacing" + fromDBQuery);

                    String queryResult = "";
                    try {
                        Statement st = conn.createStatement();
                        ResultSet rs = st.executeQuery(fromDBQuery);
                        rs.next();
                        queryResult = rs.getString(1);
                        st.close();
                    } catch (SQLException e) {
                        //that happens
                        if (e.getMessage().equals("ResultSet not positioned properly, perhaps you need to call next.")) {
                            //queryResult = "n/d";
                            //but here it is no acceptable
                            throw new MyXMLException("XLS_2_DB", "iterationVariablesProcessing", "error while getting value for variable:" + vd.getVarName(), "no query result");
                        } else {
                            throw new MySQLException(
                                    "XLS_2_DB",
                                    "iterationVariablesProcessing",
                                    fromDBQuery,
                                    "",
                                    e.getMessage());
                        }
                    }
                    LoggingFacade.trace.info("queryResult:" + queryResult);
                    vd.setValue(queryResult);
                    LoggingFacade.trace.info("" + vd);
                }
            }

            //run to DB query for 2 level variables

            for (VariableDeclaration vd : varDeclArray) {
                if (vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.UPDATE) &&
                        vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT) &&
                        vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE) &&
                        vd.isInIteration()) {
                    LoggingFacade.trace.info("2 level variable: " + vd.getVarName() + " value: " + vd.getValue()
                            + " to DB query before replacement: " + vd.getToDBQuery());

                    String toDBQuery = Util.fillQueryWithVariablesValues(vd.getToDBQuery(), varDeclArray);

                    LoggingFacade.trace.info("to DB query after replacement:" + toDBQuery);

                    try {
                        Statement st = conn.createStatement();
                        st.executeUpdate(toDBQuery);
                        st.close();
                        LoggingFacade.trace.info(toDBQuery + "succesfully made" );
                    } catch (SQLException e) {
                        throw new MySQLException(
                                "XLS_2_DB",
                                "iterationVariablesProcessing",
                                toDBQuery,
                                "",
                                e.getMessage());
                    }
                }
            }

            currentRowInTable++;
            try {
                tableHasNewRows = checkForExistanceOfDataInRow(sheet, currentRowInTable, varDeclArray, XLSVariables);
            } catch (MyException e) {
                throw e;
            } catch (Exception e) {
                LoggingFacade.trace.error("", e);
                System.exit(1);
            }
        }
    }

    private boolean checkForExistanceOfDataInRow(
            HSSFSheet sheet,
            int row,
            VariableDeclaration[] varDecl,
            XLS_Variables XLSVars) throws MyException {

        VariableDeclaration firstVariableInRow = null;
        for (VariableDeclaration vd : varDecl) {
            if (vd.isInIteration() &&
                    vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT) &&
                    vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE)) {
                firstVariableInRow = vd;
                break;
            }
        }

        int column = XLSVars.getVariableFromName(firstVariableInRow.getVarName()).getCol();
        LoggingFacade.trace.info("checkForExistanceOfDataInRow column find: " + column);

        Cell cell = null;
        try {
            cell = sheet.getRow(row).getCell(column);
        } catch (NullPointerException e){
            LoggingFacade.trace.info("end of table, NullPointerException");
            return false;
        }

        if (cell != null && Util.getCellData(cell) != null && !Util.getCellData(cell).equals("")) {
            LoggingFacade.trace.info(Util.getCellData(cell) + " in first variable in iter row");
            return true;
        } else {
            LoggingFacade.trace.info("checkForExistanceOfDataInRow : end of table");
            return false;
        }

    }
}
